﻿using System.Data.SqlClient;
using E4RPTools;
using SalesmanCommissionReport.DataSets;

namespace SalesmanCommissionReport
{
    class Database
    {
        public static VendedorDataSet GetSalesmans(SqlTransaction sqlTransaction)
        {
            var dataSet = new VendedorDataSet();
            dataSet.Vendedor.AddVendedorRow("", "");
            const string query =
                @"  select	Vendedor as Id,
                            Nome
                    from	Vendedores
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Vendedor, query);
            return dataSet;
        }

        private static bool ExistsTable(SqlTransaction sqlTransaction, string tableName, bool temporary)
        {
            const string temporaryTableQuery = "select count(*) from tempdb.sys.tables where name = @TableName";
            const string tableQuery = "select count(*) from sys.tables where name = @TableName"; 
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, temporary ? temporaryTableQuery : tableQuery, new SqlParameter("TableName", tableName)) == 1;
        }

        public static void SetupTemporaryTables(SqlTransaction sqlTransaction)
        {
            const string createTmpComissaoChefes00Query = "   CREATE TABLE ##TmpComissaoChefes00 ( " +
                                                          "       [ID] [UNIQUEIDENTIFIER]  NULL, "+
                                                          "       [Posto] [NVARCHAR] (3) COLLATE Latin1_General_CI_AS NULL, " +
                                                          "       [Chefe] [NVARCHAR] (3) COLLATE Latin1_General_CI_AS NULL, " +
                                                          "       [NomeChefe] [NVARCHAR] (35) COLLATE Latin1_General_CI_AS NULL, "+
                                                          "       [ChefeValorComissao] [FLOAT] NULL, "+
                                                          "       [Vendedor] [NVARCHAR] (3) COLLATE Latin1_General_CI_AS NULL, " +
                                                          "       [Nome] [NVARCHAR] (35) COLLATE Latin1_General_CI_AS NULL, " +
                                                          "       [VendedorTotalComissao] [FLOAT] NULL, " +
                                                          "       [PercentagemComissao] [FLOAT] NULL, " + 
                                                          "       [VendedorValorComissao] [FLOAT] NULL )";
            const string createTmpComissao00Query = "   CREATE TABLE ##TmpComissao00 ( "+
                                                    "       [ID] [UNIQUEIDENTIFIER]  NULL, "+
                                                    "       [Posto] [NVARCHAR] (3) COLLATE Latin1_General_CI_AS NULL, "+
                                                    "       [Vendedor] [NVARCHAR] (3) COLLATE Latin1_General_CI_AS NULL, " +
                                                    "       [Nome] [NVARCHAR] (35) COLLATE Latin1_General_CI_AS NULL, " +
                                                    "       [Comissao] [FLOAT] NULL, " +
                                                    "       [PercVenda] [FLOAT] NULL, " +
                                                    "       [PercLiquidacao] [FLOAT] NULL, " +
                                                    "       [Data] [DATETIME] NULL, " +
                                                    "       [Filial] [NVARCHAR] (3) COLLATE Latin1_General_CI_AS NULL, " +
                                                    "       [TipoDoc] [NVARCHAR] (5) COLLATE Latin1_General_CI_AS NULL, " +
                                                    "       [Serie] [NVARCHAR] (5) COLLATE Latin1_General_CI_AS NULL, " +
                                                    "       [NumDoc] [NVARCHAR] (20) COLLATE Latin1_General_CI_AS NULL, " +
                                                    "       [NumPrestacao] [SMALLINT] NULL, " +
                                                    "       [Entidade] [NVARCHAR] (12) COLLATE Latin1_General_CI_AS NULL, " +
                                                    "       [NomeEntidade] [NVARCHAR] (50) COLLATE Latin1_General_CI_AS NULL, " +
                                                    "       [Total] [FLOAT] NULL, " +
                                                    "       [VPago] [FLOAT] NULL, " +
                                                    "       [ComVenda] [FLOAT] NULL, " +
                                                    "       [ComLiq] [FLOAT] NULL, " + 
                                                    "       [Natureza] [NVARCHAR] (1) COLLATE Latin1_General_CI_AS NULL )";
            using (var dataAccess = new SqlDataAccess())
            {
                if (ExistsTable(sqlTransaction, "TmpComissaoChefes", false)) dataAccess.ExecuteNonQuery(sqlTransaction, "DROP TABLE TmpComissaoChefes");
                if (ExistsTable(sqlTransaction, "TmpComissao", false)) dataAccess.ExecuteNonQuery(sqlTransaction, "DROP TABLE TmpComissao");
                if (ExistsTable(sqlTransaction, "##TmpComissaoChefes00", true)) dataAccess.ExecuteNonQuery(sqlTransaction, "DROP TABLE ##TmpComissaoChefes00");
                dataAccess.ExecuteNonQuery(sqlTransaction, createTmpComissaoChefes00Query);
                if (ExistsTable(sqlTransaction, "##TmpComissao00", true)) dataAccess.ExecuteNonQuery(sqlTransaction, "DROP TABLE ##TmpComissao00");
                dataAccess.ExecuteNonQuery(sqlTransaction, createTmpComissao00Query);
                dataAccess.ExecuteNonQuery(sqlTransaction, "DELETE FROM ##TmpComissao00 WHERE Posto = '0'");
                dataAccess.ExecuteNonQuery(sqlTransaction, "DELETE FROM ##TmpComissaoChefes00 WHERE Posto = '0'");
            }
        }





        // SELECT "Vendedores"."Vendedor", "TmpComissao"."Data", "TmpComissao"."TipoDoc", "TmpComissao"."NumDoc", "TmpComissao"."Total", "TmpComissao"."VPago", "TmpComissao"."ComLiq", "TmpComissao"."ComVenda", "TmpComissao"."Natureza", "Vendedores"."ComissaoLiquidacao", "TmpComissao"."Entidade", "TmpComissao"."NumPrestacao", "TmpComissao"."Serie", "TmpComissao"."Vendedor", "TmpComissao"."Posto", "Vendedores"."Nome"
        // FROM   "##TmpComissao00" "TmpComissao" INNER JOIN "Vendedores" "Vendedores" ON "TmpComissao"."Vendedor"="Vendedores"."Vendedor"
        // WHERE  "TmpComissao"."Posto"=N'0' AND ("TmpComissao"."Vendedor">=N'101' AND "TmpComissao"."Vendedor"<=N'103 ')
        // ORDER BY "Vendedores"."Vendedor", "TmpComissao"."Vendedor", "TmpComissao"."TipoDoc", "TmpComissao"."Natureza" DESC
    }
}
